/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package tools;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import javax.servlet.jsp.JspWriter;
import system.category;
import system.subcategory;

/**
 *
 * @author Muhammad Anis
 */
public class dbconnector {

    public String connectionURL = "jdbc:mysql://localhost:3306/progin4";

    public String sqluser = "root";

    public String sqlpassword = "anis123";

    public JspWriter out;

    public dbconnector() {
        //do nothng
    }

    public Connection getConnection() {
        try {
            Class.forName("org.gjt.mm.mysql.Driver");
            Connection conn = DriverManager.getConnection(connectionURL, sqluser, sqlpassword);
            return conn;
        } catch (Exception e) {
            return null;
        }
    }

    public void register(String nama, String username, String password, String email) {

        try {
            Connection conn = getConnection();
            String query = "INSERT INTO user (username,nama,password,email) VALUES (?,?,?,?)";
            PreparedStatement pst = conn.prepareStatement(query);
            pst.setString(1, nama);
            pst.setString(2, username);
            pst.setString(3, password);
            pst.setString(4, email);
            int numRowsChange = pst.executeUpdate();

        } catch (SQLException ex) {
            Logger.getLogger(dbconnector.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public dbconnector(JspWriter out) {
        this.out = out;
    }

    public boolean login(String nama, String password) {
        try {
            Connection conn = getConnection();
            String query = "SELECT username,password FROM user "
                    + "WHERE username='" + nama + "' AND password='"
                    + password + "' AND isBan='0'";
            ResultSet rs = select(query, conn.createStatement());
            while (rs.next()) {
                String query2 = "UPDATE user SET isOnline=? WHERE username=?";
                PreparedStatement ps = conn.prepareStatement(query2);
                ps.setInt(1, 1);
                ps.setString(2, nama);
                ps.executeUpdate();
                return true;
            }


        } catch (SQLException ex) {
            Logger.getLogger(dbconnector.class.getName()).log(Level.SEVERE, null, ex);
        }
        return false;
    }

    public boolean logout(String nama) {
        try {
            Connection conn = getConnection();
            String query2 = "UPDATE user SET isOnline=? WHERE username=?";
            PreparedStatement ps = conn.prepareStatement(query2);
            ps.setInt(1, 0);
            ps.setString(2, nama);
            ps.executeUpdate();
            return true;
        } catch (SQLException ex) {
            Logger.getLogger(dbconnector.class.getName()).log(Level.SEVERE, null, ex);
        }
        return false;
    }

    public String[] getData(String nama) {
        String[] ret = new String[4];
        Connection conn = getConnection();
        String query = "SELECT * FROM user "
                + "WHERE username='" + nama + "'";
        try {
            ResultSet rs = select(query, conn.createStatement());
            while (rs.next()) {
                ret[0] = rs.getString("nama");
                ret[1] = rs.getString("email");
                ret[2] = rs.getString("maxroom");
				ret[3] = rs.getString("isBan");
            }
            return ret;
        } catch (SQLException ex) {
            Logger.getLogger(dbconnector.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    public List<String> getFriends(String username) {
        List<String> ret = new ArrayList<String>();
        Connection conn = getConnection();
        String query = "SELECT * FROM friend WHERE username='" + username + "'";
        ResultSet rs;
        try {
            rs = select(query, conn.createStatement());

            while (rs.next()) {
                ret.add(rs.getString("user_friend"));
            }
        } catch (SQLException ex) {
            Logger.getLogger(dbconnector.class.getName()).log(Level.SEVERE, null, ex);
        }
        return ret;
    }
    
    public ResultSet select(String query, Statement st) throws SQLException {
        return st.executeQuery(query);
    }    

    public void addFriend(String user1,String user2){
        Connection conn = getConnection();

        if (!isFriends(user1, user2)) {
            String query = "INSERT INTO friend (username,user_friend) VALUES (?,?)";
            PreparedStatement ps;
            try {
                ps = conn.prepareStatement(query);
                ps.setString(1, user1);
                ps.setString(2, user2);
                int executeUpdate = ps.executeUpdate();
            } catch (SQLException ex) {
                Logger.getLogger(dbconnector.class.getName()).log(Level.SEVERE, null, ex);
            }
        }


    }

    public boolean isOnline(String username){
        try {
            Connection conn = getConnection();
            String query = "SELECT isOnline FROM user WHERE username='" + username + "'";
            ResultSet rs = select(query, conn.createStatement());
            while (rs.next()) {
                if (rs.getInt("isOnline") == 1) {
                    return true;
                }
            }

        } catch (SQLException ex) {
            Logger.getLogger(dbconnector.class.getName()).log(Level.SEVERE, null, ex);
        }
        return false;
    }

    public boolean isFriends(String user1,String user2){
        try {
            Connection conn = getConnection();
            String query = "SELECT * FROM friend WHERE username='" + user1 + "' AND user_friend='"+user2+"'";
            ResultSet rs = select(query, conn.createStatement());
            while (rs.next()) {
                return true;
            }

        } catch (SQLException ex) {
            Logger.getLogger(dbconnector.class.getName()).log(Level.SEVERE, null, ex);
        }
        return false;
    }
    
    public List<category> getAllCategory(){
        try {
            List<category> ret = new LinkedList<category>();
            Connection conn = getConnection();
            String query = "SELECT * FROM kategori WHERE parent_kategori is NULL";
            ResultSet rs = select(query, conn.createStatement());
            while(rs.next()){
                String categoryname = rs.getString("nama");
                String moderator = rs.getString("moderator");
                ret.add(new category(categoryname, moderator));
            }
            return ret;
        } catch (SQLException ex) {
            Logger.getLogger(dbconnector.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    public List<subcategory> getAllSubDirectory(String categoryname){
        try {
            List<subcategory> ret = new LinkedList<subcategory>();
            Connection conn = getConnection();
            String query = "SELECT * FROM kategori WHERE parent_kategori='" + categoryname + "'";
            ResultSet rs = select(query, conn.createStatement());
            while(rs.next()){
                String nama = rs.getString("nama");
                String moderator = rs.getString("moderator");
                ret.add(new subcategory(nama, moderator));
            }
            return ret;
        } catch (SQLException ex) {
            Logger.getLogger(dbconnector.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    public List<String> getAllUser(){
        List<String> ret = new LinkedList<String>();
        try {
            Connection conn = getConnection();
            String query = "SELECT username FROM user";
            ResultSet rs = select(query, conn.createStatement());
            while(rs.next()){
                ret.add(rs.getString("username"));
            }
            return ret;
        } catch (SQLException ex) {
            Logger.getLogger(dbconnector.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    /* FUNGSI BUAT ADMINISTRATOR */

    public List<String> selectUser(String username){
        List<String> ret = new ArrayList<String>();
        Connection conn = getConnection();
        String query = "SELECT * FROM user WHERE user.username != '" + username + "'";
        ResultSet rs;
        try{
            rs = select(query, conn.createStatement());

            while (rs.next()){



                ret.add(rs.getString("username"));
            }
        } catch (SQLException ex) {
            Logger.getLogger(dbconnector.class.getName()).log(Level.SEVERE, null, ex);
        }
        return ret;
    }

    public List<String> selectKategori(){
        List<String> ret = new ArrayList<String>();
        Connection conn = getConnection();
        String query = "SELECT * FROM kategori WHERE parent_kategori IS NULL";
        ResultSet rs;
        try{
            rs = select(query, conn.createStatement());

            while (rs.next()){
                ret.add(rs.getString("idkategori"));
            }
        } catch (SQLException ex) {
            Logger.getLogger(dbconnector.class.getName()).log(Level.SEVERE, null, ex);
        }
        return ret;
    }


    public List<String> selectSubKategori(String nama){
        List<String> ret = new ArrayList<String>();
        Connection conn = getConnection();
        String query = "SELECT * FROM kategori WHERE parent_kategori = '"+nama+"'";
        ResultSet rs;
        try{

            rs = select(query, conn.createStatement());

            while (rs.next()){
                ret.add(rs.getString("idkategori"));
            }
        } catch (SQLException ex) {
            Logger.getLogger(dbconnector.class.getName()).log(Level.SEVERE, null, ex);
        }
        return ret;
    }

    public String[] getDataKategori(int id) {
        String[] ret = new String[4];
        Connection conn = getConnection();

        String query = "SELECT * FROM kategori "
                + "WHERE idkategori='" + id + "'";
        try {
            ResultSet rs = select(query, conn.createStatement());
            while (rs.next()) {
                ret[0] = rs.getString("nama");


                ret[1] = rs.getString("parent_kategori");
                ret[2] = rs.getString("moderator");
            }
            return ret;
        } catch (SQLException ex) {
            Logger.getLogger(dbconnector.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    public void addKategori(String nama,String mod){
        Connection conn = getConnection();

        String query = "INSERT INTO kategori (nama,moderator) VALUES (?,?)";
        PreparedStatement ps;
        try {
            ps = conn.prepareStatement(query);
            ps.setString(1, nama);
            ps.setString(2, mod);
            int executeUpdate = ps.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(dbconnector.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public void addKategori(String nama,String parent,String mod){
        Connection conn = getConnection();

        String query = "INSERT INTO kategori (nama,parent_kategori,moderator) VALUES (?,?,?)";
        PreparedStatement ps;
        try {
            ps = conn.prepareStatement(query);
            ps.setString(1, nama);
            ps.setString(2, parent);
            ps.setString(3, mod);
            int executeUpdate = ps.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(dbconnector.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public void dropKategori(int id){
        try {
            Connection conn = getConnection();
            String query = "DELETE FROM kategori WHERE idkategori = ?";
            PreparedStatement ps = conn.prepareStatement(query);
            ps.setInt(1, id);
            int executeUpdate = ps.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(dbconnector.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public void updateKategori(int id, String nama, String parent, String mod){
        try {
            Connection conn = getConnection();
            String query = "UPDATE kategori SET parent_kategori=?, moderator=?, nama=? WHERE idkategori=?";
            PreparedStatement ps = conn.prepareStatement(query);
            ps.setString(1, parent);
            ps.setString(2, mod);
            ps.setString(3, nama);
            ps.setInt(4, id);
            ps.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(dbconnector.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public void updateSub(String nama_baru, String parent){
        try {
            Connection conn = getConnection();
            String query = "UPDATE kategori SET parent_kategori=? WHERE parent_kategori=?";
            PreparedStatement ps = conn.prepareStatement(query);
            ps.setString(1, nama_baru);
            ps.setString(2, parent);
            ps.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(dbconnector.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public void updateKategori(int id, String nama, String mod){
        try {
            Connection conn = getConnection();
            String query = "UPDATE kategori SET moderator=?, nama=? WHERE idkategori=?";
            PreparedStatement ps = conn.prepareStatement(query);
            ps.setString(1, mod);
            ps.setString(2, nama);
            ps.setInt(3, id);
            ps.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(dbconnector.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public void updateUser (int maxRoom, String username, int isBan){
        try {
            Connection conn = getConnection();
            String query = "UPDATE user SET maxroom=?, isBan=? WHERE username=?";
            PreparedStatement ps = conn.prepareStatement(query);
            ps.setInt(1, maxRoom);
            ps.setString(3, username);
            ps.setInt(2, isBan);
            ps.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(dbconnector.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
}

